/***********************************************************************************************************************************
 *    Name: XE_AvailableEvents.sql
 * Summary: https://msdn.microsoft.com/en-us/library/dd822788(v=sql.100).aspx
 *
 * Admin
 *	Admin events are primarily geared towards database administrators and end-users. Events in this channel signal an error that
 *	generally already has a well-defined solution for it like deadlocking, or broker activation termination.
 *
 * Analytic
 *	Analytic events are geared towards performance investigations, and exist almost every place a performance counter is updated in
 *	SQL Server. They are published in high volume and describe program operation. Analytic events closely parallel existing SQL
 *	Trace events like RPC starting/completed, SP statement starting/completed, and SQL statement starting/completed.
 *
 * Debug
 *	Debug events are geared towards product support and developers. They are used to troubleshoot a specific problem and may be
 *	requested by Microsoft CSS when working on an open incident.
 *
 * Operational
 *	Operational events are also geared towards administrators and end-users, but they may also be of interest to an operations
 *	engineer or similar role. Events in this channel signal a change like database started/stopped, or attached/detached. They can
 *	be used to trigger additional tasks based on the problem being reported.
**/
SELECT  c.channel,
        p.name AS package,
        k.keyword,
        c.event,
        c.description
FROM    (SELECT event_package = o.package_guid,
                o.description,
                event = c.object_name,
                channel = v.map_value
         FROM   sys.dm_xe_objects o
                LEFT JOIN sys.dm_xe_object_columns c ON o.name = c.object_name
                INNER JOIN sys.dm_xe_map_values v ON c.type_name = v.name
                                                     AND c.column_value = CAST(v.map_key AS NVARCHAR)
         WHERE  object_type = 'event'
                AND (c.name = 'channel'
                     OR c.name IS NULL
                    )
        ) c
        LEFT JOIN (SELECT   event_package = c.object_package_guid,
                            event = c.object_name,
                            keyword = v.map_value
                   FROM     sys.dm_xe_object_columns c
                            INNER JOIN sys.dm_xe_map_values v ON c.type_name = v.name
                                                                 AND c.column_value = v.map_key
                                                                 AND c.type_package_guid = v.object_package_guid
                            INNER JOIN sys.dm_xe_objects o ON o.name = c.object_name
                                                              AND o.package_guid = c.object_package_guid
                   WHERE    object_type = 'event'
                            AND c.name = 'keyword'
                  ) k ON k.event_package = c.event_package
                         AND (k.event = c.event
                              OR k.event IS NULL
                             )
        INNER JOIN sys.dm_xe_packages p ON p.guid = c.event_package
WHERE   (p.capabilities IS NULL
         OR p.capabilities & 1 = 0
        )
ORDER BY channel,
        package,
        keyword,
        event;
GO


SELECT  p.name AS PackageName,
        o.name AS EventName,
        c.column_id,
        c.column_type,
        c.name ColumnName,
        c.column_value,
        c.description
FROM    sys.dm_xe_objects o
        INNER JOIN sys.dm_xe_packages p ON o.package_guid = p.guid
        INNER JOIN sys.dm_xe_object_columns c ON o.name = c.object_name
WHERE   o.object_type = 'event'
        AND (p.capabilities IS NULL
             OR p.capabilities <> 1
            )
ORDER BY PackageName,
        EventName,
        column_type,
        column_id;
GO